<?php
/**
* WordPress-Amfphp Integration
* @version: 0.1
* @author: Enrique Chavez aka Tmeister
* @date Jan-26, 2007
*
*/
include("awiDB.php");
class awiClient extends awiDB
{
	/*************************************************************************************
	***	OPTIONS
	*************************************************************************************/
	/**
	 * Get a option blog by name
	 * @returns String
	 * @param $name 
	 */
	function getOption($name)
	{
		parent::getDB();
		$sql = "select option_value from ".$this->wpdb->options." where option_name='$name'";
		$result = mysql_query($sql);
		$data = mysql_fetch_array($result);
		return $data[0];
	}
	/*************************************************************************************
	***	POSTS
	*************************************************************************************/
	/**
	 * Get last published posts, post_id, post_title
	 * @returns ArrayCollection
	 */
	function getRecentPosts($howMany)
	{
		parent::getDB();
		$sql = "select ID as post_id, post_title  from ".$this->wpdb->posts." where post_status='publish' order by post_date DESC limit ".$howMany;
		return mysql_query($sql);
	}
	/**
	 * Get the full data of the last published posts
	 * @param $page Started page
	 * @param $count howmanu posts by page.
	 * @returns ArrayCollection
	 */
	function getFullPosts($page, $count)
	{
		parent::getDB();
		$start = ( $page*$count );
		$sql = "select * from ".$this->wpdb->posts." where post_status='publish' order by post_date DESC limit $start, $count";
		//return $sql;
		return mysql_query($sql);
	}
	/**
	 * Get the full data of the last published posts
	 * @param $date ex. YEAR 2007, MONTH 200701 or DAY 20070130
	 * @param $page Started page
	 * @param $count howmanu posts by page.
	 * @returns ArrayCollection
	 */
	function getPostsByDate($date,$page,$count)
	{
		parent::getDB();
		$start = ( $page*$count );
		$where = '';
		$date = '' . preg_replace('|[^0-9]|', '', $date);
		$where .= ' AND YEAR(post_date)=' . substr($date, 0, 4);
		if (strlen($date)>5)
		{
			$where .= ' AND MONTH(post_date)=' . substr($date, 4, 2);
		}
		if (strlen($date)>7)
		{	
			$where .= ' AND DAYOFMONTH(post_date)=' . substr($date, 6, 2);	
		}
		$sql = "select * from ".$this->wpdb->posts." where post_status='publish' $where order by post_date DESC limit $start, $count";
		return mysql_query($sql);
	}
	/**
	 * Get all post's data by id
	 * @returns ArrayCollection
	 */
	function getPostData($postID)
	{
		parent::getDB();
		$sql = "select * from ".$this->wpdb->posts." where ID=".$postID;
		return mysql_query($sql);
	}
	/**
	 * Get post's categories ids
	 * @returns ArrayCollection
	 */
	function getPostIdCategories($postID)
	{
		parent::getDB();
		$sql = "select category_id from ".$this->wpdb->post2cat." where post_id=".$postID;
		$resource = mysql_query($sql);
		return mysql_query($sql);
	}
	/**
	 * Get post's categories name 
	 * @returns ArrayCollection
	 */
	function getPostNameCategories($postID)
	{
		parent::getDB();
		$sql = "select ".$this->wpdb->post2cat.".category_id as id, ".$this->wpdb->categories.".cat_name as name from ".$this->wpdb->post2cat.", ".$this->wpdb->categories." where ".$this->wpdb->post2cat.".category_id = ".$this->wpdb->categories.".cat_ID and ".$this->wpdb->post2cat.".post_id=$postID";
		return mysql_query($sql);
		
	}
	/*************************************************************************************
	***	COMMENTS
	*************************************************************************************/
	/**
	 * Get howmany comments have the post 
	 * @returns Number
	 */
	function getPostCommentsCount($postID)
	{
		parent::getDB();
		$sql = "select count(comment_ID) as howMany from ".$this->wpdb->comments." where comment_approved='1' and comment_post_ID=".$postID;
		$resource = mysql_query($sql);
		$row = mysql_fetch_assoc($resource);
		return $row['howMany'];
	}
	/**
	 * Get all comments by post 
	 * @returns ArrayCollection, {id, author, email, url, date, content}
	 */
	function getPostComments($postID)
	{
		parent::getDB();
		include_once("structures/comment.php");
		$sql = "select * from ".$this->wpdb->comments." where comment_approved='1' and comment_post_ID=".$postID." order by comment_date";
		$resource = mysql_query($sql);
		$comments = array();
		while( $row = mysql_fetch_assoc($resource) )
		{
			$date = date( "F jS, Y H:m" , strtotime( $row['comment_date'] ) );
			$comment = new comment();
			$comment->id = $row['comment_ID'];
			$comment->author = $row['comment_author'];
			$comment->email = $row['comment_author_email'];
			$comment->url = $row['comment_author_url'];
			$comment->date = $date;
			$comment->content = $row['comment_content'];
			$comments[] = $comment;
		}
		return $comments;
	}
	/**
	 * Get the last comments, if no parameter is set, the default number is 10 
	 * @returns ArrayCollection, {id, author, email, url, date, content}
	 */
	function getRecentComments($limit="10")
	{
		parent::getDB();
		$sql = "select ".$this->wpdb->comments.".comment_author, ".$this->wpdb->comments.".comment_author_url" .
				" ,".$this->wpdb->posts.".post_title from ".$this->wpdb->comments.",".$this->wpdb->posts." " .
				"where ".$this->wpdb->comments.".comment_approved ='1' " .
				"and ".$this->wpdb->comments.".comment_post_ID=".$this->wpdb->posts.".ID " .
				"order by comment_date DESC limit $limit";		
		
		return mysql_query($sql);
	}
	/*************************************************************************************
	***	ARCHIVES, CATEGORIES, BLOGROLL
	*************************************************************************************/
	/**
	 * Get Archives tree order by Year, Month
	 * ej.
	 * 2007
	 *	/- January
	 *  /- 01
	 *  /-2007
	 *2006
	 *	/- December
	 *	/- 12
	 *  /-2006
	 * @returns ArrayCollection 
	 */
	function getFullArchives()
	{
		parent::getDB();
		$now = date("Y-m-d H:i:s");
		$results = array();
		$result = mysql_query("SELECT DISTINCT YEAR(post_date) AS 'year', MONTH(post_date) AS 'month', count(ID) as posts"
								. " FROM ".$this->wpdb->posts
								. " WHERE post_date < '".$now."'"
								. " AND post_status = 'publish'"
								. " GROUP BY YEAR(post_date), MONTH(post_date)"
								. " ORDER BY post_date DESC");
		;
		while($row = mysql_fetch_assoc($result))
		{
			$row['posts'] = array();
			$startmonth = $row['year']."-".$this->_zeroise($row['month'], 2)."-01 00:00:00";
			$endmonth = $this->_silpstream_add_month($startmonth, 1);
			$sql = "SELECT ID AS 'ID', post_date AS 'post_date', post_title AS 'title'"
										. " FROM ".$this->wpdb->posts
										. " WHERE post_date > '".$startmonth."'"
										. " AND post_date < '".$endmonth."'"
										. " AND post_status = 'publish'"
										. " ORDER BY post_date DESC";
			$posts = mysql_query($sql);
			while( $post = mysql_fetch_assoc ($posts) )
			{
				 array_push($row['posts'], $post);
			}
			array_push($results, $row);
		}
		return $this->_doTree($results);
	}
	/**
	 * Get the blog's Archives
	 * @param String $type [monthly, yearly, daily]
	 * @param Number $limit 
	 */
	function getArchives($type, $limit="")
	{
		parent::getDB();
		if( strlen(trim( $limit ) ) )
		{
			$limit = "limit $limit";
		}else
		{
			$limit = "";
		}
		switch($type) 
		{
			case "monthly":
				$sql = "SELECT DISTINCT YEAR(post_date) AS 'year' , MONTH(post_date) AS 'month' , count(ID) as posts "
					. " FROM " .$this->wpdb->posts." WHERE post_type = 'post' AND post_status = 'publish' "
					. " GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC " .$limit;
				return  mysql_query($sql);
				break;
			case "yearly":
				$sql = "SELECT DISTINCT YEAR(post_date) AS `year`, count(ID) as posts" 
						. " FROM " .$this->wpdb->posts." WHERE post_type ='post' AND post_status = 'publish' "
						. " GROUP BY YEAR(post_date) ORDER BY post_date DESC " . $limit;
				//return $sql;
				return mysql_query($sql);
				break;
			case "daily":
				$sql = "SELECT DISTINCT YEAR(post_date) AS 'year', MONTH(post_date) " .
						"AS 'month', DAYOFMONTH(post_date) AS 'dayofmonth', count(ID) as posts " .
						"FROM " .$this->wpdb->posts." WHERE post_type = 'post' AND post_status = 'publish' " .
						"GROUP BY YEAR(post_date), MONTH(post_date), " .
						"DAYOFMONTH(post_date) ORDER BY post_date DESC " . $limit;
				return mysql_query($sql);
				break;
		}
	}
	/**
	 * Get the Blog's Categories
	 * @param Boolean $count [If true return the count posts by categorie, if false return only the categorie name]
	 */
	function getCategories($count=false)
	{
		parent::getDB();
		$count = ($count) ? ", category_count as count" : "";
		$sql = "select cat_name as name, cat_ID as id $count  from ".$this->wpdb->categories." ,".$this->wpdb->post2cat .
				" where ".$this->wpdb->post2cat.".category_id  = ".$this->wpdb->categories.".cat_ID " .
				"group by cat_name order by cat_name";
		return mysql_query($sql);
	}
	/**
	 * Get all post order by Category
	 * ej.
	 * [0]
	 * 	 title : ActionScript
	 * 	 children : Object
	 * 		[0]
	 * 			post_id : 1
	 * 			title : post Title
	 * @returns ArrayCollection
	 */
	function getAllPostByCategory()
	{
		parent::getDB();
		$sql = ("SELECT cat_ID, cat_name, category_parent, category_count"
						. " FROM ".$this->wpdb->categories
						. " WHERE cat_ID > 0 and category_count > 0"
						. " ORDER BY cat_name");
		$result = mysql_query($sql);
		$data = array();
		while ( $row = mysql_fetch_assoc($result) )
		{
			$temp = array();
			$temp['title'] = utf8_decode($row['cat_name']);
			$temp['children'] = array();
			$sql = "Select post_id from ".$this->wpdb->post2cat." where category_id='".$row['cat_ID']."' order by post_id DESC";
			$postResult = mysql_query($sql);
			while($post = mysql_fetch_assoc($postResult))
			{
				$sql = "select post_title as title, ID from ".$this->wpdb->posts." where ID=".$post['post_id']." and post_status='publish'";
				$resP = mysql_query($sql);
				if( $rowP = mysql_fetch_assoc($resP) )
				{
					$kill = array("title" => utf8_decode($rowP['title']), "post_id" => $rowP['ID']);
					array_push ( $temp['children'], $kill );  
				}
			} 
			array_push($data, $temp);
		}
		//print_r($data);
		return $data;
	}
	/**
	 * Get all links in the blogroll, if a limit parameter is passed, returns randoms links
	 * @param Number $limit 
	 * @returns ArrayCollection
	 */
	function getBlogRoll($limit="")
	{
		parent::getDB();
		if($limit == ""){
			$sql = "select * from ".$this->wpdb->links." where link_visible='Y' order by link_name";
		}else
		{
			$sql = "select * from ".$this->wpdb->links." where link_visible='Y' order by rand() limit $limit";
		}
		return mysql_query($sql);
	}
	function _doTree($src)
	{
		$months = array('','January','February','March','April','May','June','July','August','September','October','November','December');
		$year = 0;
		$resultFinal = array();
		$result = "";
		foreach (  $src as $row )
		{
			if($row['year'] != $year )
			{
				if($year > 0){
					array_push($resultFinal, $result);	
				}
				$year = $row['year'];
				$month = "";
				$result = array("title" => $year, "children" => array());
				$push = ($year != 0) ? true : false;
				
			}
			$tmonth = array("title" => $months[$row['month']], "month" => ($row['month'] < 10 ) ? "0".$row['month'] : $row['month'], "year" => $year );
			array_push($result['children'],  $tmonth);
		}
		array_push($resultFinal, $result);
		return $resultFinal;
	}
	function _zeroise($number,$threshold)
	{
		return sprintf('%0'.$threshold.'s', $number);
	}
	function _silpstream_add_month($datestring, $nummonths='1')
	{
		$curtimestamp = strtotime($datestring);
		$newdatestring = date("Y-m-d H:i:s", mktime(0, 0, 0, date("m", $curtimestamp)+$nummonths, date("d", $curtimestamp),  date("Y", $curtimestamp)));
		return $newdatestring;
	}
}
?>